package com.iplant.aps.serviceimpl.dao.sfc;

import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.sfc.SFCOutMaterialConfig;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class SFCOutMaterialConfigDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(SFCOutMaterialConfigDAO.class);

    private static SFCOutMaterialConfigDAO Instance = null;

    private SFCOutMaterialConfigDAO() {
        super();
    }

    public static SFCOutMaterialConfigDAO getInstance() {
        if (Instance == null)
            Instance = new SFCOutMaterialConfigDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, SFCOutMaterialConfig wSFCOutMaterialConfig,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wSFCOutMaterialConfig == null)
                return 0;

            String wSQL = "";
            if (wSFCOutMaterialConfig.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.sfc_outmaterialconfig(ProductID,LineID,MaterialID," +
                                "SuplierID,RepairTimes,IsPartCode,CreateID,CreateTime,EditID,EditTime,Active," +
                                "ComponentName,PartID) VALUES" +
                                "(:ProductID,:LineID,:MaterialID,:SuplierID,:RepairTimes,:IsPartCode,:CreateID," +
                                ":CreateTime,:EditID,:EditTime,:Active,:ComponentName,:PartID);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.sfc_outmaterialconfig SET ProductID = :ProductID,LineID = " +
                                ":LineID,MaterialID = :MaterialID,SuplierID = :SuplierID,RepairTimes = :RepairTimes," +
                                "IsPartCode = :IsPartCode,CreateID = :CreateID,CreateTime = :CreateTime,EditID = " +
                                ":EditID,EditTime = :EditTime,Active = :Active,ComponentName=:ComponentName," +
                                "PartID=:PartID WHERE ID " +
                                "= :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wSFCOutMaterialConfig.ID);
            wParamMap.put("ProductID", wSFCOutMaterialConfig.ProductID);
            wParamMap.put("LineID", wSFCOutMaterialConfig.LineID);
            wParamMap.put("MaterialID", wSFCOutMaterialConfig.MaterialID);
            wParamMap.put("SuplierID", wSFCOutMaterialConfig.SuplierID);
            wParamMap.put("RepairTimes", wSFCOutMaterialConfig.RepairTimes);
            wParamMap.put("IsPartCode", wSFCOutMaterialConfig.IsPartCode);
            wParamMap.put("CreateID", wSFCOutMaterialConfig.CreateID);
            wParamMap.put("CreateTime", wSFCOutMaterialConfig.CreateTime);
            wParamMap.put("EditID", wSFCOutMaterialConfig.EditID);
            wParamMap.put("EditTime", wSFCOutMaterialConfig.EditTime);
            wParamMap.put("Active", wSFCOutMaterialConfig.Active);
            wParamMap.put("ComponentName", wSFCOutMaterialConfig.ComponentName);
            wParamMap.put("PartID", wSFCOutMaterialConfig.PartID);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wSFCOutMaterialConfig.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wSFCOutMaterialConfig.setID(wResult);
            } else {
                wResult = wSFCOutMaterialConfig.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<SFCOutMaterialConfig> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (SFCOutMaterialConfig wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.sfc_outmaterialconfig WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public SFCOutMaterialConfig SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        SFCOutMaterialConfig wResult = new SFCOutMaterialConfig();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<SFCOutMaterialConfig> wList = SelectList(wLoginUser, wID, -1, -1, -1, -1, -1, -1, null, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<SFCOutMaterialConfig> SelectList(BMSEmployee wLoginUser, int wID, int wProductID, int wLineID,
                                                 int wSuplierID, int wIsPartCode, int wActive, int wPartID,
                                                 List<Integer> wMaterialIDList,
                                                 OutResult<Integer> wErrorCode) {
        List<SFCOutMaterialConfig> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wMaterialIDList == null || wMaterialIDList.size() <= 0) {
                wMaterialIDList = new ArrayList<>();
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.ProductNo,t2.Name LineName,t3.MaterialNo,t3.MaterialName,t4.SupplierName,t4" +
                            ".SupplierCode,t5.Name Creator,t6.Name Editor,t7.Name PartName FROM " +
                            "{0}" +
                            ".sfc_outmaterialconfig t "
                            + " left join {0}.fpc_product t1 on t.ProductID=t1.ID "
                            + " left join {0}.fmc_line t2 on t.LineID=t2.ID "
                            + " left join {0}.mss_material t3 on t.MaterialID=t3.ID "
                            + " left join {0}.scm_supplier t4 on t.SuplierID=t4.ID "
                            + " left join {0}.mbs_user t5 on t.CreateID=t5.ID "
                            + " left join {0}.mbs_user t6 on t.EditID=t6.ID "
                            + " left join {0}.fpc_part t7 on t.PartID=t7.ID "
                            + "WHERE  1=1  and ( :wID <= 0 or :wID = t.ID ) and ( " +
                            ":wProductID <= 0 or :wProductID = t.ProductID ) and ( :wLineID <= 0 or :wLineID = t" +
                            ".LineID )" + " "
                            + "and ( :wSuplierID <= 0 or :wSuplierID = t.SuplierID ) "
                            + "AND (:MaterialID=null or :MaterialID='''' or t.MaterialID IN ({1})) "
                            + "and ( :wPartID <= 0 or :wPartID = t.PartID ) " +
                            "and ( :wIsPartCode <= 0 or " +
                            ":wIsPartCode = t.IsPartCode ) and ( :wActive <= 0 or :wActive = t.Active );",
                    wInstance.Result, wMaterialIDList.size() > 0 ?
                            StringUtils.Join(",", wMaterialIDList) : "0");

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wSuplierID", wSuplierID);
            wParamMap.put("wIsPartCode", wIsPartCode);
            wParamMap.put("wActive", wActive);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("MaterialID", StringUtils.Join(",", wMaterialIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                SFCOutMaterialConfig wItem = new SFCOutMaterialConfig();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.SuplierID = StringUtils.parseInt(wReader.get("SuplierID"));
                wItem.SuplierName = StringUtils.parseString(wReader.get("SupplierName"));
                wItem.SuplierCode = StringUtils.parseString(wReader.get("SupplierCode"));
                wItem.RepairTimes = StringUtils.parseDouble(wReader.get("RepairTimes"));
                wItem.IsPartCode = StringUtils.parseInt(wReader.get("IsPartCode"));
                wItem.CreateID = StringUtils.parseInt(wReader.get("CreateID"));
                wItem.Creator = StringUtils.parseString(wReader.get("Creator"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.EditID = StringUtils.parseInt(wReader.get("EditID"));
                wItem.Editor = StringUtils.parseString(wReader.get("Editor"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.Active = StringUtils.parseInt(wReader.get("Active"));
                wItem.ComponentName = StringUtils.parseString(wReader.get("ComponentName"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public boolean IsRepeat(BMSEmployee wLoginUser, SFCOutMaterialConfig wSFCOutMaterialConfig,
                            OutResult<Integer> wErrorCode) {
        boolean wResult = false;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return false;
            }

            String wSQL = StringUtils.Format("SELECT count(*) FQTY FROM {0}.sfc_outmaterialconfig " +
                            "where ComponentName=:ComponentName and MaterialID=:MaterialID and PartID=:PartID and " +
                            "ID!=:ID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ComponentName", wSFCOutMaterialConfig.ComponentName);
            wParamMap.put("MaterialID", wSFCOutMaterialConfig.MaterialID);
            wParamMap.put("PartID", wSFCOutMaterialConfig.PartID);
            wParamMap.put("ID", wSFCOutMaterialConfig.ID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wFQTY = StringUtils.parseInt(wReader.get("FQTY"));
                if (wFQTY > 0) {
                    wResult = true;
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public Map<Integer, Integer> GetIDMap(BMSEmployee wLoginUser, List<Integer> wIDList, int wPartID, int wOrderID,
                                          OutResult<Integer> wErrorCode) {
        Map<Integer, Integer> wResult = new HashMap<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wIDList == null || wIDList.size() <= 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT IPTItemID,ID FROM {0}.ipt_outsourcingmaterial WHERE " +
                            "OrderID=:OrderID AND PartID=:PartID AND IPTItemID IN({1});",
                    wInstance.Result, StringUtils.Join(",", wIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("OrderID", wOrderID);
            wParamMap.put("PartID", wPartID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wIPTItemID = StringUtils.parseInt(wReader.get("IPTItemID"));
                int wID = StringUtils.parseInt(wReader.get("ID"));
                wResult.put(wIPTItemID, wID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查询有当前标准的委外拆修部件ID集合
     */
    public List<Integer> SelectHasStandardConfigIDList(BMSEmployee wLoginUser, List<Integer> wIDList,
                                                       OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wIDList == null || wIDList.size() <= 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT distinct OutPartID FROM {0}.wms_qtstandard where OutPartID in ({1}) and IsCurrent=1;",
                    wInstance.Result, StringUtils.Join(",", wIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wOutPartID = StringUtils.parseInt(wReader.get("OutPartID"));
                wResult.add(wOutPartID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
